﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class ArticlesProvider : DataAccess
    {
        public ArticlesProvider()
        {
            this.ConnectionString = Globals.Settings.ConnectionString;
            this.EnableCaching = Globals.Settings.Articles.EnableCaching;
            this.CacheDuration = Globals.Settings.Articles.CacheDuration;
        }

        #region Chuyển IDataReader --> Group Article

        #region IDataReader --> Article
        /// <summary>
        /// Returns a new ArticleDetails instance filled with the DataReader's current record data
        /// </summary>
        protected virtual ArticleDetails GetArticleFromReader(IDataReader reader)
        {
            return GetArticleFromReader(reader, true);
        }
        protected virtual ArticleDetails GetArticleFromReader(IDataReader reader, bool readBody)
        {
            ArticleDetails article = new ArticleDetails(
               (int)reader["ArticleID"],
               (DateTime)reader["AddedDate"],
               reader["AddedBy"].ToString(),
               (int)reader["CategoryID"],
               reader["Title"].ToString(),
               reader["Abstract"].ToString(),
               null,
               (reader["ReleaseDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["ReleaseDate"]),
               (reader["ExpireDate"] == DBNull.Value ? DateTime.MaxValue : (DateTime)reader["ExpireDate"]),
               (bool)reader["Approved"],
               (bool)reader["Listed"],
               (bool)reader["CommentsEnabled"],
               (bool)reader["OnlyForMembers"],
               (int)reader["ViewCount"],
               (int)reader["Votes"],
               (int)reader["TotalRating"],
               reader["ImageUrl"].ToString());

            if (readBody)
                article.Body = reader["Body"].ToString();

            return article;
        }

        /// <summary>
        /// Returns a collection of ArticleDetails objects with the data read from the input DataReader
        /// </summary>
        protected virtual List<ArticleDetails> GetArticleCollectionFromReader(IDataReader reader)
        {
            return GetArticleCollectionFromReader(reader, true);
        }
        protected virtual List<ArticleDetails> GetArticleCollectionFromReader(IDataReader reader, bool readBody)
        {
            List<ArticleDetails> articles = new List<ArticleDetails>();
            while (reader.Read())
                articles.Add(GetArticleFromReader(reader, readBody));
            return articles;
        }
        #endregion

        #region IDataReader --> Category
        /// <summary>
        /// Returns a new CategoryDetails instance filled with the DataReader's current record data
        /// </summary>
        protected virtual CategoryDetails GetCategoryFromReader(IDataReader reader)
        {
            return new CategoryDetails(
               (int)reader["CategoryID"],
               (DateTime)reader["AddedDate"],
               reader["AddedBy"].ToString(),
               reader["Title"].ToString(),
               (int)reader["Importance"],
               reader["Description"].ToString(),
               reader["ImageUrl"].ToString());
        }

        /// <summary>
        /// Returns a collection of CategoryDetails objects with the data read from the input DataReader
        /// </summary>
        protected virtual List<CategoryDetails> GetCategoryCollectionFromReader(IDataReader reader)
        {
            List<CategoryDetails> categories = new List<CategoryDetails>();
            while (reader.Read())
                categories.Add(GetCategoryFromReader(reader));
            return categories;
        }
        #endregion

        #region IDataReader --> Comment
        /// <summary>
        /// Returns a new CommentDetails instance filled with the DataReader's current record data
        /// </summary>
        protected virtual CommentDetails GetCommentFromReader(IDataReader reader)
        {
            return new CommentDetails(
               (int)reader["CommentID"],
               (DateTime)reader["AddedDate"],
               reader["AddedBy"].ToString(),
               reader["AddedByEmail"].ToString(),
               reader["AddedByIP"].ToString(),
               (int)reader["ArticleID"],
               reader["ArticleTitle"].ToString(),
               reader["Body"].ToString());
        }

        /// <summary>
        /// Returns a collection of CommentDetails objects with the data read from the input DataReader
        /// </summary>
        protected virtual List<CommentDetails> GetCommentCollectionFromReader(IDataReader reader)
        {
            List<CommentDetails> comments = new List<CommentDetails>();
            while (reader.Read())
                comments.Add(GetCommentFromReader(reader));
            return comments;
        }
        #endregion

        #endregion

        #region Method Store --> Group Article

        #region Store -- Article
        /// <summary>
        /// Retrieves all articles
        /// </summary>
        public List<ArticleDetails> ListArticles(int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }

        /// <summary>
        /// Returns the total number of articles
        /// </summary>
        public int GetArticleCount()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("GetArticleCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves all articles for the specified category
        /// </summary>
        public List<ArticleDetails> ListArticles(int categoryID, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesGetByCategories", cn);
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }

        /// <summary>
        /// Returns the total number of articles for the specified category
        /// </summary>
        public int GetArticleCount(int categoryID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetArticleCountByCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves all published articles
        /// </summary>
        public List<ArticleDetails> ListPublishedArticles(DateTime currentDate, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetPublishedArticles", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = currentDate;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }

        /// <summary>
        /// Returns the total number of published articles
        /// </summary>
        public int GetPublishedArticleCount(DateTime currentDate)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetPublishedArticleCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = currentDate;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves all published articles for the specified category
        /// </summary>
        public List<ArticleDetails> ListPublishedArticles(int categoryID, DateTime currentDate, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetPublishedArticlesByCategory", cn);
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = currentDate;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetArticleCollectionFromReader(ExecuteReader(cmd), false);
            }
        }

        /// <summary>
        /// Returns the total number of published articles for the specified category
        /// </summary>
        public int GetPublishedArticleCount(int categoryID, DateTime currentDate)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetPublishedArticleCountByCategory", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cmd.Parameters.Add("@CurrentDate", SqlDbType.DateTime).Value = currentDate;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves the article with the specified ID
        /// </summary>
        public ArticleDetails GetArticleByID(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetArticleFromReader(reader, true);
                else
                    return null;
            }
        }

        /// <summary>
        /// Retrieves the body for the article with the specified ID
        /// </summary>
        public string GetArticleBody(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_GetArticleBody", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                return (string)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Deletes an article
        /// </summary>
        public bool DeleteArticle(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a new article
        /// </summary>
        public int InsertArticle(ArticleDetails article)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = article.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = article.AddedBy;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = article.CategoryID;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = article.Title;
                cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = article.Abstract;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = article.Body;
                cmd.Parameters.Add("@ReleaseDate", SqlDbType.DateTime).Value = article.ReleaseDate;
                cmd.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = article.ExpireDate;
                cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = article.Approved;
                cmd.Parameters.Add("@Listed", SqlDbType.Bit).Value = article.Listed;
                cmd.Parameters.Add("@CommentsEnabled", SqlDbType.Bit).Value = article.CommentsEnabled;
                cmd.Parameters.Add("@OnlyForMembers", SqlDbType.Bit).Value = article.OnlyForMembers;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = article.ImageUrl;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@ArticleID"].Value;
            }
        }

        /// <summary>
        /// Updates an article
        /// </summary>
        public bool UpdateArticle(ArticleDetails article)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ArticlesUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = article.ID;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = article.CategoryID;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = article.Title;
                cmd.Parameters.Add("@Abstract", SqlDbType.NVarChar).Value = article.Abstract;
                cmd.Parameters.Add("@Body", SqlDbType.NText).Value = article.Body;
                cmd.Parameters.Add("@ReleaseDate", SqlDbType.DateTime).Value = article.ReleaseDate;
                cmd.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = article.ExpireDate;
                cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = article.Approved;
                cmd.Parameters.Add("@Listed", SqlDbType.Bit).Value = article.Listed;
                cmd.Parameters.Add("@CommentsEnabled", SqlDbType.Bit).Value = article.CommentsEnabled;
                cmd.Parameters.Add("@OnlyForMembers", SqlDbType.Bit).Value = article.OnlyForMembers;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = article.ImageUrl;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Approves an article
        /// </summary>
        public bool ApproveArticle(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_ApproveArticle", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Increments the ViewCount of the specified article
        /// </summary>
        public bool IncrementArticleViewCount(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_IncrementViewCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a vote for the specified article
        /// </summary>
        public bool RateArticle(int articleID, int rating)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Articles_InsertVote", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = rating;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion

        #region Store -- Category
        /// <summary>
        /// Returns a collection with all the categories
        /// </summary>
        public List<CategoryDetails> ListCategories()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CategoriesList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCategoryCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns an existing category with the specified ID
        /// </summary>
        public CategoryDetails GetCategoryByID(int categoryID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CategoriesGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetCategoryFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes a category
        /// </summary>
        public bool DeleteCategory(int categoryID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CategoriesDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Updates a category
        /// </summary>
        public bool UpdateCategory(CategoryDetails category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CategoriesUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = category.ID;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = category.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = category.Importance;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = category.ImageUrl;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = category.Description;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Creates a new category
        /// </summary>
        public int InsertCategory(CategoryDetails category)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CategoriesInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = category.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = category.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = category.Title;
                cmd.Parameters.Add("@Importance", SqlDbType.Int).Value = category.Importance;
                cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = category.ImageUrl;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = category.Description;
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@CategoryID"].Value;
            }
        }

        #endregion

        #region Store -- Comment
        /// <summary>
        /// Retrieves all comments
        /// </summary>
        public List<CommentDetails> ListComments(int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsList", cn);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCommentCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns the total number of comments
        /// </summary>
        public int GetCommentCount()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Comments_GetCommentCount", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves all comments for the specified article
        /// </summary>
        public List<CommentDetails> ListCommentByArticle(int articleID, int pageIndex, int pageSize)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsGetByArticles", cn);
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCommentCollectionFromReader(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Returns the total number of comments for the specified article
        /// </summary>
        public int GetCommentCountByArticle(int articleID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("Comments_GetCommentCountByArticle", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID;
                cn.Open();
                return (int)ExecuteScalar(cmd);
            }
        }

        /// <summary>
        /// Retrieves the comment with the specified ID
        /// </summary>
        public CommentDetails GetCommentByID(int commentID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CommentID", SqlDbType.Int).Value = commentID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetCommentFromReader(reader);
                else
                    return null;
            }
        }

        /// <summary>
        /// Deletes a comment
        /// </summary>
        public bool DeleteComment(int commentID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CommentID", SqlDbType.Int).Value = commentID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Inserts a new comment
        /// </summary>
        public int InsertComment(CommentDetails comment)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = comment.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = comment.AddedBy;
                cmd.Parameters.Add("@AddedByEmail", SqlDbType.NVarChar).Value = comment.AddedByEmail;
                cmd.Parameters.Add("@AddedByIP", SqlDbType.NVarChar).Value = comment.AddedByIP;
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = comment.ArticleID;
                cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = comment.Body;
                cmd.Parameters.Add("@CommentID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@CommentID"].Value;
            }
        }

        /// <summary>
        /// Updates an comment
        /// </summary>
        public bool UpdateComment(CommentDetails comment)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("CommentsUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CommentID", SqlDbType.Int).Value = comment.ID;
                cmd.Parameters.Add("@Body", SqlDbType.NVarChar).Value = comment.Body;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion


        #endregion
    }
}
